﻿Option Strict On
Option Explicit On

Imports CObject
Imports ConnectionServer
Imports System.Data.SqlClient
Imports System.Web
Imports Objects.Utility


Namespace CMT

    Public Class MeetingAttendee
        Inherits CObject.CObject

        Public Sub New()
            MyBase.New()
        End Sub

        Public Sub New(ByVal piAttendeeID As Integer)
            MyBase.New(piAttendeeID)
        End Sub

        Public Overrides Function Clone() As CObject.CObject
            Return New MeetingAttendee()
        End Function

        Public Overrides ReadOnly Property Key As String
            Get
                Return "AttendeeID"
            End Get
        End Property

        Protected Overrides Function ProcedureSigniture(ByVal peCommandType As CObject.CommandType) As String
            Select Case peCommandType
                Case CommandType.SQLSelect
                    Return "EXEC CMT_GetMeetingAttendeesDetails {EventID} "
                Case CommandType.SQLInsert
                    Return "EXEC CMT_MeetingAttendeesAdd {XmlString} "
                Case Else
                    Throw New SystemException("Invalid Command Type")
            End Select
        End Function

        Public Overrides ReadOnly Property SourceDataBase As ConnectionServer.Connection.SourceDataBase
            Get
                Return Connection.SourceDataBase.CMT
            End Get
        End Property

#Region "Properties"

        Public Property Output() As Integer
            Get
                Return CastToInteger(GetProperty("Output"))
            End Get
            Set(ByVal Value As Integer)
                SetProperty("Output", Value)
            End Set
        End Property

        Public ReadOnly Property AttendeeID As Integer
            Get
                Return CastToInteger(GetProperty("AttendeeID"))
            End Get
        End Property

        Public Property EventID As Integer
            Get
                Return CastToInteger(GetProperty("EventID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("EventID", value)
            End Set
        End Property

        Public Property MeetingID As Integer
            Get
                Return CastToInteger(GetProperty("MeetingID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("MeetingID", value)
            End Set
        End Property

        Public Property EventAttendeeID As Integer
            Get
                Return CastToInteger(GetProperty("EventAttendeeID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("EventAttendeeID", value)
            End Set
        End Property

        Public ReadOnly Property EventAttendeeObj As EventAttendee
            Get
                Dim loObj As EventAttendee = New EventAttendee(EventAttendeeID)
                If loObj.EventAttendeeID <> 0 Then
                    Return loObj
                Else
                    'no data
                    Return Nothing
                End If
            End Get
        End Property

        Public Property StartDate As Date
            Get
                Return CastToDate(GetProperty("StartDate"))
            End Get
            Set(ByVal value As Date)
                SetProperty("StartDate", value)
            End Set
        End Property

        Public Property EndDate As Date
            Get
                Return CastToDate(GetProperty("EndDate"))
            End Get
            Set(ByVal value As Date)
                SetProperty("EndDate", value)
            End Set
        End Property

        Public Property XmlString As String
            Get
                Return CastToString(GetProperty("XmlString"))
            End Get
            Set(ByVal value As String)
                SetProperty("XmlString", value)
            End Set
        End Property

#End Region

#Region "Methods"

        Public Overrides Sub Save()
            Dim loTran As New CTransaction()
            loTran.HasOutPut = True
            AddHandler loTran.TransactionOutput, AddressOf OuputHandler
            loTran.AddObject(Me)
            loTran.Save()
        End Sub

        Private Sub OuputHandler(ByVal value As Object)
            Output = CInt(value)
        End Sub

        ''' <summary>
        ''' Method to get the Meeting Attendee details.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetMeetingAttendeeDetailsForEvent(ByVal piEventID As Integer) As DataTable
            Dim loDT As DataTable

            If HttpContext.Current.Session("MeetingAttendeesDetails") Is Nothing Then
                Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                    Dim lsSQl As String = String.Empty
                    lsSQl = "EXEC CMT_GetMeetingAttendeesDetails " + piEventID.ToString()
                    loDT = loConn.GetDataTable(lsSQl)
                    HttpContext.Current.Session("MeetingAttendeesDetails") = loDT
                End Using
            Else
                loDT = CType(HttpContext.Current.Session("MeetingAttendeesDetails"), System.Data.DataTable)
            End If

            Return loDT
        End Function

        ''' <summary>
        ''' Method to get the room details for an event.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetRoomDetailsForEvent(ByVal piEventID As Integer) As DataTable
            Dim loDT As DataTable

            If HttpContext.Current.Session("RoomDetails") Is Nothing Then
                Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                    Dim lsSQl As String = String.Empty
                    lsSQl = "EXEC CMT_GetRoomsDetails " + piEventID.ToString()
                    loDT = loConn.GetDataTable(lsSQl)
                    HttpContext.Current.Session("RoomDetails") = loDT
                End Using
            Else
                loDT = CType(HttpContext.Current.Session("RoomDetails"), System.Data.DataTable)
            End If

            Return loDT
        End Function


        ''' <summary>
        ''' Method to get the room details for an event.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetMeetingAttendeeCntryMap(ByVal piEventAttendeeID As Integer, ByVal piMeetingId As Integer) As DataTable
            Dim loDT As DataTable

            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                Dim lsSQl As String = String.Empty
                lsSQl = "EXEC CMT_GetMeetingAttendeeCntryMap " + piEventAttendeeID.ToString() + "," + piMeetingId.ToString()
                loDT = loConn.GetDataTable(lsSQl)
            End Using
            Return loDT
        End Function

        ''' <summary>
        ''' Method to update attendee for meeting.
        ''' </summary>
        ''' <param name="meetingAttendee"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function UpdateMeetingAttendee(ByVal meetingId As Integer, ByVal meetingAttendee As String) As Integer
            Dim retParam As SqlDataReader
            Dim retParamOut As Integer
            Dim intCounter As Integer
            Dim loDT As DataTable = New DataTable()
            Dim column As DataColumn = New DataColumn("Name")
            loDT.Columns.Add(column)
            Dim column1 As DataColumn = New DataColumn("To")
            loDT.Columns.Add(column1)
            Dim column2 As DataColumn = New DataColumn("Subject")
            loDT.Columns.Add(column2)
            Dim column3 As DataColumn = New DataColumn("Body")
            loDT.Columns.Add(column3)

            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pMeetingId", SqlDbType.Int)
                loCommand.Parameters.Add("@pAttendeesXmlString", SqlDbType.VarChar)

                loCommand.Parameters("@pMeetingId").Value = meetingId
                loCommand.Parameters("@pAttendeesXmlString").Value = meetingAttendee

                ' Add Output Parameter
                Dim loParamInstance1 As SqlParameter = New SqlParameter("@Output", SqlDbType.Int)
                loParamInstance1.Direction = ParameterDirection.Output
                loCommand.Parameters.Add(loParamInstance1)

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_MeetingAttendeesUpdate"

                'Execute the Query
                retParam = loCommand.ExecuteReader()
                While retParam.Read()
                    Dim dr As DataRow = loDT.NewRow()
                    For intCounter = 0 To retParam.FieldCount - 1
                        dr(intCounter) = retParam.GetValue(intCounter)
                    Next
                    loDT.Rows.Add(dr)
                End While

                loConn.Connection.Close()

                If IsDBNull(loCommand.Parameters("@Output").Value) Then
                    retParamOut = 0
                Else
                    retParamOut = Convert.ToInt32(loCommand.Parameters("@Output").Value)
                End If
            End Using
            Return retParamOut
        End Function

        Private Function SendMailToAttendee(ByVal meetingDetails As DataTable) As Boolean
            Try
                Dim loSetting As Settings = New Settings(Connection.SourceDataBase.CMT)
                Dim loEmail As Email = New Email(CInt(loSetting("CMT-MeetingNotificationEmailId").ToString))
                Dim liCount As Integer
                Dim Subject As String
                Dim Body As String
                Subject = loEmail.Subject
                Body = loEmail.BodyText

                With loEmail

                    For liCount = 0 To (meetingDetails.Rows.Count - 1)
                        .Subject = Subject.Replace("~Subject", meetingDetails.Rows(liCount)("Subject").ToString)
                        .BodyText = Body.Replace("~Name", meetingDetails.Rows(liCount)("Name").ToString).Replace("~Body", meetingDetails.Rows(liCount)("Body").ToString)
                        loEmail.SendEmail(meetingDetails.Rows(liCount)("To").ToString)
                    Next

                End With
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        ''' <summary>
        ''' Method to add attendee for meeting.
        ''' </summary>
        ''' <param name="meetingAttendee"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function AddMeetingAttendee(ByVal meetingAttendee As String) As Integer
            Dim retParam As Integer
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pXmlString", SqlDbType.VarChar)

                loCommand.Parameters("@pXmlString").Value = meetingAttendee

                ' Add Output Parameter
                Dim loParamInstance1 As SqlParameter = New SqlParameter("@Output", SqlDbType.Int)
                loParamInstance1.Direction = ParameterDirection.Output
                loCommand.Parameters.Add(loParamInstance1)

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_MeetingAttendeesAdd"

                'Execute the Query
                retParam = loCommand.ExecuteNonQuery()
                loConn.Connection.Close()

                If IsDBNull(loCommand.Parameters("@Output").Value) Then
                    retParam = 0
                Else
                    retParam = Convert.ToInt32(loCommand.Parameters("@Output").Value)
                End If

                Return retParam
            End Using
        End Function
#End Region

    End Class

End Namespace